
#region Imports

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

#endregion

namespace Justa.Data.Reflection
{
    /// <summary>
    /// 
    /// </summary>
    /// 
    public class SchemaReflector : GenericDataAccess
    {
        /// <summary>
        /// 
        /// </summary>
        /// 
        /// <param name="database"></param>
        /// 
        public SchemaReflector(string database) 
            : base(database)
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// 
        public static readonly string GetStoredProcedures =
                @"SELECT SPECIFIC_NAME AS SPName, ROUTINE_DEFINITION AS SQL, CREATED AS CreatedOn, LAST_ALTERED AS ModifiedOn 
                        FROM INFORMATION_SCHEMA.ROUTINES
                        WHERE ROUTINE_TYPE = 'PROCEDURE' 
                        AND SPECIFIC_NAME NOT LIKE 'sp_%diagram%'";

        /// <summary>
        /// 
        /// </summary>
        /// 
        public static readonly string GetStoredProcedureParamters = 
                @"SELECT SPECIFIC_NAME AS SPName, ORDINAL_POSITION AS OrdinalPosition, PARAMETER_MODE AS ParamType, IS_RESULT AS IsResult, PARAMETER_NAME AS Name, DATA_TYPE AS DataType, CHARACTER_MAXIMUM_LENGTH AS DataLength, REPLACE(PARAMETER_NAME, '@', '') AS CleanName, PARAMETER_MODE as [mode]
                        FROM INFORMATION_SCHEMA.PARAMETERS";

        /// <summary>
        /// 
        /// </summary>
        /// 
        public static readonly string GetTables =
                @"SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS Name, TABLE_TYPE 
                        FROM INFORMATION_SCHEMA.TABLES
                        WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> N'sysdiagrams') AND (TABLE_NAME <> N'dtproperties')";

        /// <summary>
        /// 
        /// </summary>
        /// 
        public void DisplayStoredProcedures()
        {
            using (IDataReader reader = GetQueryDataReader(GetStoredProcedures))
            {
                while (reader.Read())
                {
                    DisplayStoredProcedureParameters(reader[0] as string);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// 
        /// <param name="storedProcedureName"></param>
        /// 
        public void DisplayStoredProcedureParameters(string storedProcedureName)
        {
            DataTable table = new DataTable();
            table.Load(GetQueryDataReader(GetStoredProcedureParamters));

            DataView view = new DataView(table);
            view.RowFilter = string.Format("SPName = '{0}'", storedProcedureName);
            view.Sort = "OrdinalPosition";

            using (IDataReader reader = view.ToTable().CreateDataReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("SPName = {0}, Ordinal Position = {1}, Param Mode = {2}, IS Result = {3}, Param Name = {4}, Data Type = {5}, Char Max Length = {6}, Clean Name = {7}, Mode = {8}", reader[0], reader[1], reader[2], reader[3], reader[4], reader[5], reader[6], reader[7], reader[8]);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// 
        public void GetTableNameList()
        {
            StringBuilder sList = new StringBuilder();
            using (IDataReader rdr = GetQueryDataReader(GetTables))
            {
                while (rdr.Read())
                {
                    Console.WriteLine("Table: {0}", rdr[SqlSchemaVariable.NAME]);
                }
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// 
    public class SqlSchemaVariable
    {
        public const string PARAMETER_PREFIX = "@";
        public const string NAME = "Name";
        public const string COLUMN_NAME = "ColumnName";
        public const string COLUMN_DEFAULT = "DefaultSetting";
        public const string TABLE_NAME = "TableName";
        public const string DATA_TYPE = "DataType";
        public const string MAX_LENGTH = "MaxLength";
        public const string MODE = "mode";
        public const string MODE_INOUT = "INOUT";
        public const string IS_NULLABLE = "IsNullable";
        public const string CONSTRAINT_TYPE = "constraintType";
        public const string IS_IDENTITY = "isIdentity";
        public const string PRIMARY_KEY = "PRIMARY KEY";
        public const string FOREIGN_KEY = "FOREIGN KEY";
    }
}
